<?PHP
/* $Id: flags.php 7 2009-03-24 20:25:52Z ronan $ */
//Modified by Slyferz

session_start();
//getting the user's access to see if they are allowed
include("includes/paxsql.php");
mysql_close();

include 'includes/database.php';
include 'includes/library.php';
include 'includes/noTsList.php';

function renderFlagsNavi() {
	if ($_GET['flag'] == 1 || !isset($_GET['flag'])) echo '<a href="?flag=1"><b>Inactive</b></a>';
	else echo '<a href="?flag=1">Inactive</a>';
	echo ' | ';
	if ($_GET['flag'] == 2) echo '<a href="?flag=2"><b>Alt No Main</b></a>';
	else echo '<a href="?flag=2">Alt No Main</a>';
	echo ' | ';
	if ($_GET['flag'] == 3) echo '<a href="?flag=3"><b>AFK</b></a>';
	else echo '<a href="?flag=3">AFK</a>';
	echo ' | ';
	if ($_GET['flag'] == 4) echo '<a href="?flag=4"><b>Notes</b></a>';
	else echo '<a href="?flag=4">Notes</a>';
	echo ' | ';
	if ($_GET['flag'] == 5) echo '<a href="?flag=5"><b>Investigate</b></a>';
	else echo '<a href="?flag=5">Investigate</a>';
	echo ' | ';
	if ($_GET['flag'] == 6) echo '<a href="?flag=6"><b>Probation</b></a>';
	else echo '<a href="?flag=6">Probation</a>';
	echo ' | ';
	if ($_GET['flag'] == 7) echo '<a href="?flag=7"><b>Afk Alts</b></a>';
	else echo '<a href="?flag=7">Afk Alts</a>';
	echo ' | ';
	if ($_GET['flag'] == 8) echo '<a href="?flag=8"><b>No TS</b></a>';
	else echo '<a href="?flag=8">No TS</a>';
	echo ' | ';
	if ($_GET['flag'] == 9) echo '<a href="?flag=9"><b>No TZ</b></a>';
	else echo '<a href="?flag=9">No TZ</a>';
}

function listInactive() {
	$query = "SELECT characters.charID, characters.name, characters.lastSeen, characters.inactive
		FROM characters LEFT JOIN alts
		ON characters.charID = alts.charID
		WHERE alts.charID IS NULL
		AND characters.inCorp = 1
		AND characters.inactive = 1
		ORDER BY characters.name";
	$results = mysql_query($query);
	$numInactive = mysql_num_rows($results);
	
	echo "There are $numInactive mains inactive.<br><br>";
	
	echo "<table class=\"main\"><tr><td class=\"main\" width=\"70%\"><b>Name</b></td><td class=\"main\" width=\"30%\"><b>Last Seen</b></td></tr>";
	
	while ($char = mysql_fetch_array($results)) {
		/*** RENDER NAME ***/
		echo "<tr style=\"background: #ffa9a9;\">";
			
		echo "<td class=\"main\">";
		renderName($char['charID']);
		echo "</td>";
		
		/*** RENDER LAST SEEN ***/
		echo "<td class=\"main\">";
		$timePassed = eveTime() - $char['lastSeen'];
		
		if ($timePassed < 3600) echo "-" . round($timePassed/60) . " mins";
		else if ($timePassed < 172800) echo "-" . round($timePassed/3600, 2) . " hrs";
		else echo "-" . round($timePassed/86400, 2) . " days";	
		echo "</td></tr>";
	}
	echo "</table>";
}

function listAltNoMain() {
	$query = "SELECT characters.charID AS altID, characters.lastSeen, characters.name AS altName,
		mains.charID AS mainID, mains.name as mainName
		FROM characters
		LEFT JOIN alts ON characters.charID = alts.charID
		LEFT JOIN characters AS mains ON alts.altOf = mains.charID
		WHERE alts.charID IS NOT NULL
		AND characters.inCorp = 1
		AND mains.inCorp = 0";
	$results = mysql_query($query);
	$numAltNoMain = mysql_num_rows($results);
	
	echo "There are $numAltNoMain alts in corp without mains.<br><br>";
	
	if ($numAltNoMain > 0) {
		echo "<table class=\"main\"><tr><td class=\"main\" width=\"70%\"><b>Name</b></td><td class=\"main\" width=\"30%\"><b>Last Seen</b></td></tr>";
		
		while ($char = mysql_fetch_array($results)) {
			/*** RENDER NAME ***/
			if ($char['inactive'] == 1)
				echo "<tr style=\"background: #ffa9a9;\">";
			elseif (eveTime() - $char['lastSeen'] > 60*60*24*15)
				echo "<tr style=\"background: #fdff5e;\">";
			elseif (eveTime() - $char['joined'] < 60*60*24*30)
				echo "<tr style=\"background: #b9f0b9;\">";
			else
				echo "<tr>";
				
			echo "<td class=\"main\">";
			renderName($char['altID']);
			echo "</td>";
			
			/*** RENDER LAST SEEN ***/
			echo "<td class=\"main\">";
			$timePassed = eveTime() - $char['lastSeen'];
			
			if ($timePassed < 3600) echo "-" . round($timePassed/60) . " mins";
			else if ($timePassed < 172800) echo "-" . round($timePassed/3600, 2) . " hrs";
			else echo "-" . round($timePassed/86400, 2) . " days";	
			echo "</td></tr>";
		}
		echo "</table>";
	}
}

function listAFK() {
	$query = "SELECT characters.charID, characters.name, characters.lastSeen, characters.inactive
		FROM characters LEFT JOIN alts
		ON characters.charID = alts.charID
		WHERE alts.charID IS NULL
		AND characters.inCorp = 1
		AND characters.afk = 1
		ORDER BY characters.name";
	$results = mysql_query($query);
	$numNotes = mysql_num_rows($results);
	
	echo "There are $numNotes mains AFK.<br><br>";
	
	echo "<table class=\"main\"><tr><td class=\"main\" width=\"70%\"><b>Name</b></td><td class=\"main\" width=\"30%\"><b>Last Seen</b></td></tr>";
	
	while ($char = mysql_fetch_array($results)) {
		/*** RENDER NAME ***/
		if ($char['inactive'] == 1)
			echo "<tr style=\"background: #ffa9a9;\">";
		elseif (eveTime() - $char['lastSeen'] > 60*60*24*15)
			echo "<tr style=\"background: #fdff5e;\">";
		elseif (eveTime() - $char['joined'] < 60*60*24*30)
			echo "<tr style=\"background: #b9f0b9;\">";
		else
			echo "<tr>";
			
		echo "<td class=\"main\">";
		renderName($char['charID']);
		echo "</td>";
		
		/*** RENDER LAST SEEN ***/
		echo "<td class=\"main\">";
		$timePassed = eveTime() - $char['lastSeen'];
		
		if ($timePassed < 3600) echo "-" . round($timePassed/60) . " mins";
		else if ($timePassed < 172800) echo "-" . round($timePassed/3600, 2) . " hrs";
		else echo "-" . round($timePassed/86400, 2) . " days";	
		echo "</td></tr>";
	}
	echo "</table>";
}

function listNotes() {
	$query = "SELECT characters.charID, characters.name, characters.lastSeen, characters.inactive
		FROM characters LEFT JOIN alts
		ON characters.charID = alts.charID
		WHERE alts.charID IS NULL
		AND characters.inCorp = 1
		AND characters.notes != ''
		ORDER BY characters.name";
	$results = mysql_query($query);
	$numNotes = mysql_num_rows($results);
	
	echo "There are $numNotes notes.<br><br>";
	
	echo "<table class=\"main\"><tr><td class=\"main\" width=\"70%\"><b>Name</b></td><td class=\"main\" width=\"30%\"><b>Last Seen</b></td></tr>";
	
	while ($char = mysql_fetch_array($results)) {
		/*** RENDER NAME ***/
		if ($char['inactive'] == 1)
			echo "<tr style=\"background: #ffa9a9;\">";
		elseif (eveTime() - $char['lastSeen'] > 60*60*24*15)
			echo "<tr style=\"background: #fdff5e;\">";
		elseif (eveTime() - $char['joined'] < 60*60*24*30)
			echo "<tr style=\"background: #b9f0b9;\">";
		else
			echo "<tr>";
			
		echo "<td class=\"main\">";
		renderName($char['charID']);
		echo "</td>";
		
		/*** RENDER LAST SEEN ***/
		echo "<td class=\"main\">";
		$timePassed = eveTime() - $char['lastSeen'];
		
		if ($timePassed < 3600) echo "-" . round($timePassed/60) . " mins";
		else if ($timePassed < 172800) echo "-" . round($timePassed/3600, 2) . " hrs";
		else echo "-" . round($timePassed/86400, 2) . " days";	
		echo "</td></tr>";
	}
	echo "</table>";
}

function listInvestigate() {
	$query = "SELECT characters.charID, characters.name, characters.lastSeen, characters.inactive
		FROM characters LEFT JOIN alts
		ON characters.charID = alts.charID
		WHERE alts.charID IS NULL
		AND characters.inCorp = 1
		AND characters.investigate = 1
		ORDER BY characters.name";
	$results = mysql_query($query);
	$numNotes = mysql_num_rows($results);
	
	echo "There are $numNotes under investigation.<br><br>";
	
	echo "<table class=\"main\"><tr><td class=\"main\" width=\"70%\"><b>Name</b></td><td class=\"main\" width=\"30%\"><b>Last Seen</b></td></tr>";
	
	while ($char = mysql_fetch_array($results)) {
		/*** RENDER NAME ***/
		if ($char['inactive'] == 1)
			echo "<tr style=\"background: #ffa9a9;\">";
		elseif (eveTime() - $char['lastSeen'] > 60*60*24*15)
			echo "<tr style=\"background: #fdff5e;\">";
		elseif (eveTime() - $char['joined'] < 60*60*24*30)
			echo "<tr style=\"background: #b9f0b9;\">";
		else
			echo "<tr>";
			
		echo "<td class=\"main\">";
		renderName($char['charID']);
		echo "</td>";
		
		/*** RENDER LAST SEEN ***/
		echo "<td class=\"main\">";
		$timePassed = eveTime() - $char['lastSeen'];
		
		if ($timePassed < 3600) echo "-" . round($timePassed/60) . " mins";
		else if ($timePassed < 172800) echo "-" . round($timePassed/3600, 2) . " hrs";
		else echo "-" . round($timePassed/86400, 2) . " days";	
		echo "</td></tr>";
	}
	echo "</table>";
}

function listProbation() {
	$query = "SELECT characters.charID, characters.name, characters.lastSeen, characters.inactive
		FROM characters LEFT JOIN Jobs
		ON characters.charID = Jobs.charID
		WHERE characters.inCorp = 1
		AND Jobs.probation = 1
		ORDER BY characters.name";
	$results = mysql_query($query) or die(mysql_error());
	$numProbate = mysql_num_rows($results);
	
	echo "There are $numProbate member(s) on probation.<br><br>";
	
	echo "<table class=\"main\"><tr><td class=\"main\" width=\"70%\"><b>Name</b></td><td class=\"main\" width=\"30%\"><b>Last Seen</b></td></tr>";
	
	while ($char = mysql_fetch_array($results)) {
		/*** RENDER NAME ***/
		if ($char['inactive'] == 1)
			echo "<tr style=\"background: #ffa9a9;\">";
		elseif (eveTime() - $char['lastSeen'] > 60*60*24*15)
			echo "<tr style=\"background: #fdff5e;\">";
		elseif (eveTime() - $char['joined'] < 60*60*24*30)
			echo "<tr style=\"background: #b9f0b9;\">";
		else
			echo "<tr>";
			
		echo "<td class=\"main\">";
		renderName($char['charID']);
		echo "</td>";
		
		/*** RENDER LAST SEEN ***/
		echo "<td class=\"main\">";
		$timePassed = eveTime() - $char['lastSeen'];
		
		if ($timePassed < 3600) echo "-" . round($timePassed/60) . " mins";
		else if ($timePassed < 172800) echo "-" . round($timePassed/3600, 2) . " hrs";
		else echo "-" . round($timePassed/86400, 2) . " days";	
		echo "</td></tr>";
	}
	echo "</table>";
}

function listAfkAlts()
{
	//atm 150 days inactive
	$query = 'SELECT DISTINCT characters.charID, characters.name, characters.lastSeen
		FROM characters LEFT JOIN alts
		ON characters.charID = alts.charID
		WHERE alts.charID IS NOT NULL
		AND characters.inCorp = 1
		AND characters.lastSeen < '.(time() - (150*24*60*60)."
		ORDER BY characters.lastSeen");
	$result = mysql_query($query) or die(mysql_error());
	$numAfk = mysql_num_rows($result);

	echo 'There are '.$numAfk.' alt(s) that are inactive';
	echo("<table class = 'main'><tr><td class = 'main' width = '70%'><b>Name</b></td><td class = 'main'><b>Last Seen</b></td></tr>");

	while($row = mysql_fetch_array($result))
	{
		echo("<tr><td class = 'main'>");
		renderName($row['charID']);
		echo("</td>");
		echo("<td class = 'main'>".round((eveTime() - $row['lastSeen'])/86400, 2)." days</td></tr>");
	}
	echo("</table>");
}

function listNoTs()
{
	$tsList = getTsList();
	for($i = 0; !empty($tsList[$i]); $i++)
	{
		if($i == 0)
			$tsString = $tsList[$i];
		else
			$tsString = ($tsString.",".$tsList[$i]);
	}	
	//getting the main characters not on the ts list
	/*$query = 'SELECT characters.name, characters.charID, characters.lastSeen FROM characters
		WHERE characters.charID IN('.$tsString.')
		AND characters.inCorp = 1
		ORDER BY characters.name';*/
	$query = 'SELECT characters.charID 
		FROM characters LEFT JOIN alts
		ON characters.charID = alts.charID
		WHERE characters.charID NOT IN('.$tsString.')
		AND alts.charID IS NULL
		AND characters.inCorp = 1';
	/*$query = 'SELECT DISTINCT characters.charID, characters.name, characters.lastSeen
		FROM characters LEFT JOIN alts
		ON characters.charID = alts.charID
		WHERE characters.inCorp = 1
		AND characters.charID NOT IN('.$tsString.')
		AND alts.charID IS NULL
		ORDER BY characters.name';*/
	$result = mysql_query($query) or die(mysql_error());

	$tsString2 = convertID($result, 'charID');
	
	//getting the mains whos alts are on the list, but whos mains are not
	$query = 'SELECT DISTINCT alts.altOf
		FROM alts
		WHERE alts.charID IN('.$tsString.')
		AND alts.altOf IN('.$tsString2.')';
	$result = mysql_query($query) or die(mysql_error());
	$tsString3 = convertID($result, 'altOf');

	$query = 'SELECT DISTINCT characters.charID, characters.name, characters.lastSeen
		FROM characters LEFT JOIN alts
		ON characters.charID = alts.altOf
		WHERE characters.inCorp = 1
		AND characters.charID IN('.$tsString2.')
		AND characters.charID NOT IN('.$tsString3.')
		ORDER BY characters.name';
	$result = mysql_query($query) or die(mysql_error());
	$numNoTs = mysql_num_rows($result);

	echo("There are ".$numNoTs." people not signed up for TS");
	echo("<table class = 'main'><tr><td class = 'main' width = '70%'><b>Name</b></td><td class = 'main'><b>Last Seen</b></td></tr>");

	while($row = mysql_fetch_array($result))
	{
		echo("<tr><td class = 'main'");
		renderName($row['charID']);
		echo("</td>");
		$timePassed = eveTime() - $row['lastSeen'];

		echo "<td class=\"main\">";
		if ($timePassed < 3600) echo "-" . round($timePassed/60) . " mins";
		else if ($timePassed < 172800) echo "-" . round($timePassed/3600, 2) . " hrs";
		else echo "-" . round($timePassed/86400, 2) . " days";
		echo("</td></tr>");
	}
	echo("</table>");
}

function listNoTz()
{
	$query = "SELECT DISTINCT characters.charID, characters.name, characters.lastSeen 
		FROM `characters` 
		WHERE tz = 0
	       	AND characters.inCorp = 1	
		AND charID NOT IN(SELECT charID FROM alts)
		ORDER BY characters.name";
	$result = mysql_query($query);
	$numNoTz = mysql_num_rows($result);
	echo("There are ".$numNoTz." people that do not have a listed timezone");
	echo("<table class = 'main'><tr><td class = 'main' width = '70%'><b>Name</b></td><td class = 'main'><b>Last Seen</b></td></tr>");
	while($row = mysql_fetch_array($result))
	{
		echo("<tr><td class = 'main'>");
		renderName($row['charID']);
		echo("</td>");
		$timePassed = eveTime() - $row['lastSeen'];

		echo("<td class = 'main'>");
		if($timePassed < 3600) echo "-" . round($timePassed/60) . " mins";
		else if($timePassed < 172800) echo "-" . round($timePassed/3600, 2) . " hrs";
		else echo "-" . round($timePassed/86400, 2) . " days";
		echo("</td></tr>");
	}
	echo("</table>");
}

function convertID($result, $parameter)
{
	for($i = 0; $row = mysql_fetch_array($result); $i++)
	{
		if($i == 0)
			$tsString = $row[$parameter];
		else
			$tsString = ($tsString.",".$row[$parameter]);
	}
	return $tsString;
}

if($access > 1 )
{
echo("
<html>
<head>
<link rel='stylesheet' type='text/css' href='main.css'>
<title>snowflake</title>");

include('includes/javascript.php');
echo("
</head>

<body>

<div id='isAltWin' style='display: none;'></div>
<div id='flagWin' style='display: none;'></div>

<div id='wrapper'>

	<div id='header'><a href='/'>snowflake</a></div>

	<div id='navi'>");
include('includes/navi.php');
echo("</div>

	<div id='main'>
		<h1>flags</h1>
		<hr>");

renderFlagsNavi();
echo("<br><br>"); 
	if ($_GET['flag'] == 2) listAltNoMain();
	elseif ($_GET['flag'] == 3) listAFK();
	elseif ($_GET['flag'] == 4) listNotes();
	elseif ($_GET['flag'] == 5) listInvestigate();
	elseif ($_GET['flag'] == 6) listProbation();
	elseif ($_GET['flag'] == 7) listAfkAlts();
	elseif ($_GET['flag'] == 8) listNoTs();
	elseif ($_GET['flag'] == 9) listNoTz();
	else listInactive();
echo("
	</div>
	
</div>

</body>

</html>");
}
//they do not belong here, they get the boot
else
{
	header("Location: http://".$_SERVER['HTTP_HOST']."/index.php");
}
?>
